/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.thienloc.omap.jdbc.dao;

import com.thienloc.omap.jdbc.entity.SmsBroadcasting;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 *
 * @author GiangLT
 */
public class SmsBroadcastingDao extends BaseDao {

    protected DbAccess dbAccess = DbAccess.getInstance();
    
    public List<String> findSmsByDay(Date startDate, Date endDate) throws Exception {
        List<String> result = new ArrayList<String>();
        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;
        try {
            SimpleDateFormat partitionDf = new SimpleDateFormat("yyyyMM");
            String partitionName = "P" + partitionDf.format(startDate);
            String sql =
                    "select distinct SMS from TBL_SMS_BROADCASTING "
                    + " partition (" + partitionName + ")"
                    + " where sent_time >= ? and sent_time <= ? order by SMS";
            conn = dbAccess.getConn();
            ps = conn.prepareStatement(sql);

            SimpleDateFormat dateFormat = new SimpleDateFormat(SmsBroadcasting.DATE_PATTERN);
            ps.setString(1, dateFormat.format(startDate));
            ps.setString(2, dateFormat.format(endDate));

            rs = ps.executeQuery();
            while (rs.next()) {
                result.add(rs.getString(1));
            }
        } catch (Exception ex) {
            throw ex;
        } finally {
            DbAccess.closeResultSet(rs);
            DbAccess.closePreparedStatement(ps);
            DbAccess.closeConn(conn);
        }

        return result;
    }

    public List<String> findSmsByDay(Date date) throws Exception {
        Date startDate = new Date(date.getTime());
        startDate.setHours(0);
        startDate.setMinutes(0);
        startDate.setSeconds(0);

        Date endDate = new Date(date.getTime());
        endDate.setHours(23);
        endDate.setMinutes(59);

        return findSmsByDay(startDate, endDate);
    }

    public List<String> findMsisdnByDay(String sms, Date startDate, Date endDate) throws Exception {
        List<String> result = new ArrayList<String>();
        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;
        try {
            SimpleDateFormat partitionDf = new SimpleDateFormat("yyyyMM");
            String partitionName = "P" + partitionDf.format(startDate);
            String sql =
                    "select distinct MSISDN from TBL_SMS_BROADCASTING "
                    + " partition (" + partitionName + ")"
                    + " where sent_time >= ? and sent_time <= ? and sms = ? order by MSISDN";
            conn = dbAccess.getConn();
            ps = conn.prepareStatement(sql);

            SimpleDateFormat dateFormat = new SimpleDateFormat(SmsBroadcasting.DATE_PATTERN);
            ps.setString(1, dateFormat.format(startDate));
            ps.setString(2, dateFormat.format(endDate));
            ps.setString(3, sms);

            rs = ps.executeQuery();
            while (rs.next()) {
                result.add(rs.getString(1));
            }
        } catch (Exception ex) {
            throw ex;
        } finally {
            DbAccess.closeResultSet(rs);
            DbAccess.closePreparedStatement(ps);
            DbAccess.closeConn(conn);
        }

        return result;
    }

    public List<String> findMsisdnByDay(String sms, Date date) throws Exception {
        Date startDate = new Date(date.getTime());
        startDate.setHours(0);
        startDate.setMinutes(0);
        startDate.setSeconds(0);

        Date endDate = new Date(date.getTime());
        endDate.setHours(23);
        endDate.setMinutes(59);
        endDate.setSeconds(59);

        return findMsisdnByDay(sms, startDate, endDate);
    }

    public BroadcastStatistic getBroadcastStatistic(String sms, Date date) throws Exception {
        BroadcastStatistic result = null;
        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;
        try {
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMM");
            String partitionName = "P" + dateFormat.format(date);
            String sql =
                    "select count(*), max(sent_time), min(sent_time) from TBL_SMS_BROADCASTING "
                    + " partition (" + partitionName + ")"
                    + " where sms = ?"
                    + " and sent_time >= ?"
                    + " and sent_time <= ?";
            conn = dbAccess.getConn();
            ps = conn.prepareStatement(sql);
            ps.setString(1, sms);

            dateFormat = new SimpleDateFormat(SmsBroadcasting.DATE_PATTERN);
            Date startDate = new Date(date.getTime());
            startDate.setHours(0);
            startDate.setMinutes(0);
            startDate.setSeconds(0);
            ps.setString(2, dateFormat.format(startDate));

            Date endDate = new Date(date.getTime());
            endDate.setHours(23);
            endDate.setMinutes(59);
            endDate.setSeconds(59);
            ps.setString(3, dateFormat.format(endDate));

            rs = ps.executeQuery();
            if (rs.next()) {
                result = new BroadcastStatistic();
                result.setShootingCount(rs.getInt(1));
                result.setEndShootingTime(rs.getString(2));
                result.setStartShootingTime(rs.getString(3));
            }
        } catch (Exception ex) {
            throw ex;
        } finally {
            DbAccess.closeResultSet(rs);
            DbAccess.closePreparedStatement(ps);
            DbAccess.closeConn(conn);
        }

        return result;
    }

    public class BroadcastStatistic {

        private Integer shootingCount;
        private String startShootingTime;
        private String endShootingTime;

        public BroadcastStatistic() {
        }

        public BroadcastStatistic(Integer shootingCount, String startShootingTime, String endShootingTime) {
            this.shootingCount = shootingCount;
            this.startShootingTime = startShootingTime;
            this.endShootingTime = endShootingTime;
        }

        public String getEndShootingTime() {
            return endShootingTime;
        }

        public void setEndShootingTime(String endShootingTime) {
            this.endShootingTime = endShootingTime;
        }

        public Integer getShootingCount() {
            return shootingCount;
        }

        public void setShootingCount(Integer shootingCount) {
            this.shootingCount = shootingCount;
        }

        public String getStartShootingTime() {
            return startShootingTime;
        }

        public void setStartShootingTime(String startShootingTime) {
            this.startShootingTime = startShootingTime;
        }
    }
}
